package edu.hhu.dynamic.table;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class TableDao {
	//依据查询条件获取table数据
	public String getData(String tablename,String nodeStr,String fieldStr,String s_date,String e_date,
			String start,String limit){	
		String sql=getFy(tablename,nodeStr, fieldStr, s_date, e_date,start,limit);
		String total=getTotalSql(tablename,nodeStr, fieldStr, s_date, e_date);


		List<Record> data = Db.use("mysql").find(sql);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			jsonList.add(record.getColumns());
			i++;
		}
		
		String json = JSON.toJSONString(jsonList);
		json="{\"total\":"+total+",\"items\":"+json+"}";
//		System.out.println(i+"||"+json);
		return json;
	}
	
	//获取总的条数
	public String getTotalSql(String tablename,String nodeStr,String fieldStr,String s_date,String e_date){
		String sql=getTableSql(tablename,nodeStr, fieldStr, s_date, e_date);
		sql="select count(*) total from ("+sql.substring(0, sql.length()-1)+") as tt;";
		List<Record> data1 = Db.use("mysql").find(sql);
		String total="";
		for(Record record:data1){
			total=record.getColumns().get("total").toString();
		}
		return total;
		
	}
	
	//分页   返回sql语句
	public String getFy(String tablename,String nodeStr,String fieldStr,String s_date,String e_date,
			String start,String limit){
		String sql=getTableSql(tablename,nodeStr, fieldStr, s_date, e_date);
		sql="select * from ("+sql.substring(0, sql.length()-1)+
				" order by SCRQ "+") as tt limit "+start+","+limit+";";
		return sql;
	}
	
	//根据fieldStr获取 返回sql语句
		public String getTableSql(String tablename,String nodeStr,String fieldStr,String s_date,String e_date){
			String sql="";	
			//井号信息
			String nodesCon=" WELLCODE='"+nodeStr+"' "; 	
			//井属性信息
			String[] fields=fieldStr.split(",");
			String fieldConn="";
			for(int i=0;i<fields.length;i++){
				fieldConn=fieldConn+fields[i]+",";
			}
			fieldConn=fieldConn.substring(0, fieldConn.length()-1);
			sql="SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ,"+fieldConn
				+" FROM "+tablename+"  WHERE SCRQ BETWEEN '"+s_date+"' AND '"+e_date
				+"' and "+nodesCon+";";
			return sql;
		}
		
		//根据fieldStr获取 返回sql语句
		public String getExcelSql(String tablename,String nodeStr,String fieldStr,String s_date,String e_date){
			String sql="";	
			//井号信息
			String[] nodes=nodeStr.split(",");
			String nodesCon="(";
			for(int i=0;i<nodes.length;i++){
				nodesCon+=" WELLCODE='"+nodes[i]+"' or";
			}
			nodesCon=nodesCon.substring(0,nodesCon.length()-3);
			nodesCon=nodesCon+")";		
			//井属性信息
			String[] fields=fieldStr.split(",");
			String fieldConn="";
			for(int i=0;i<fields.length;i++){
				fieldConn=fieldConn+fields[i]+",";
			}
			fieldConn=fieldConn.substring(0, fieldConn.length()-1);
			sql="SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ,"+fieldConn
				+" FROM "+tablename+"  WHERE SCRQ BETWEEN '"+s_date+"' AND '"+e_date
				+"' and "+nodesCon+";";
			return sql;
		}
		
	
    public List getHaeder(){
    	String sql="select fieldcode,concat(fieldname,'（',unitname,'）') fieldname from dictionary ;";
    	List<Record> data = Db.use("mysql").find(sql);
    	List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			jsonList.add(record.getColumns());
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		return jsonList;
    }


}
